package com.health.trans.repository;

import com.alibaba.fastjson.JSONObject;
import com.health.entity.OpcSeeDoctor;
import com.health.entity.OpcSeeDoctorPK;
import com.health.trans.util.CustomRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import java.util.List;

/**
 * Repository
 * @author qyh
*/
public interface OpcSeeDoctorRepository extends CustomRepository<OpcSeeDoctor, OpcSeeDoctorPK>, JpaSpecificationExecutor<OpcSeeDoctor> {
    /**
     * 得到json转换列表
     *
     * @param hospCode    hosp代码
     * @param visitDetailsId 就诊明细ID
     * @return {@link List}<{@link JSONObject}>
     */
    @Query(nativeQuery = true, value = GET_CONVERT_JSON_SQL)
    JSONObject getConvertJson(@Param("hospCode") String hospCode,
                              @Param("visitDetailsId") String visitDetailsId);


    String GET_CONVERT_JSON_SQL ="select \n" +
            "!ISNULL(ipd.hosp_code) as doctorAdvice ,\n" +
            "!ISNULL(a.hosp_code) as opcPrescription ,\n" +
            "!ISNULL(ck.hosp_code) as checkApply,\n" +
            "!ISNULL(ta.hosp_code) as testApply,\n" +
            "!ISNULL(sa.hosp_code) as surgeryApply "+
            "from (select * from tb_opc_see_doctor  where  hosp_code=:hospCode and visit_details_id=:visitDetailsId) t \n" +
            "LEFT JOIN \n" +
            "tb_ip_doctor_advice ipd  on ipd.hosp_code=t.hosp_code and ipd.treatment_no=t.treatment_no \n" +
            "left join tb_opc_prescription a  on a.hosp_code=t.hosp_code and a.treatment_no=t.treatment_no \n" +
            "and a.prescription_category!='02' and a.prescription_category!='03' and a.prescription_category!='04'\n" +
            "LEFT JOIN tb_ckeck_apply ck on ck.hosp_code=t.hosp_code and ck.treatment_no=t.treatment_no\n" +
            "LEFT JOIN tb_test_apply ta on ta.hosp_code=t.hosp_code and ta.treatment_no=t.treatment_no\n" +
            "LEFT JOIN tb_surgery_apply sa on sa.hosp_code=t.hosp_code and sa.treatment_no=t.treatment_no " +
            "GROUP BY t.hosp_code,t.visit_details_id;";

}
